#coding=utf-8
from pysqlite2 import dbapi2 as sqlite
import Queue
import vglobals,logger,time
from vglobals import DATABASE as db

DBFILE = 'index/index.db'

'''
SQL

'''
INS_DICT = u'insert into dict values(NULL,?,?)'
INS_POS  = 'insert into pos values(NULL,?)'
INS_DOC  = u'insert into doc values(NULL,?)'
INS_SNAP = u'insert into snap values(NULL,?,?,?,?,?)'
DEL_DICT = 'delete * from dict where 0=0'
UPD_POS = 'update pos set posstr=? where posid=?'
SEL_ALL_DICT = 'select * from dict'
SEL_MAX_POS  = 'select max(posid) from pos'
SEL_POS = 'select * from pos where posid=?'
SEL_DOC = 'select * from doc where docid=?'
SEL_DOC_NAME = u'select * from doc where docpath=?'
SEL_WORD = u'select * from dict where word=?'
SEL_POS_BY_WORD = u'select posstr from pos where posid=(select posid from dict where word=?)'
SEL_MAP = u'select word,posstr from dict,pos where dict.posid=pos.posid'
SEL_MAX_DOCID = 'select max(docid) from doc'
SEL_DOCPATH = u'select docpath from doc where docid=?'
SEL_SNAP = u'select content,encoding,timestamp from snap where docid=?'
SEL_DOC_COUNT = 'select count(*) from doc'
SEL_LAST_INDEXD = 'select max(docid) from doc'
SEL_CHK_HASH = u'select * from snap where dochash=?'    

class database:
    def __init__(self):
        try:
           self.con = sqlite.connect(DBFILE,isolation_level='IMMEDIATE',timeout=3)
           self.con.text_factory=str
           self.cur = self.con.cursor()
           result = self.cur.execute(SEL_ALL_DICT).fetchall()
        except Exception,ex:
           if str(ex) == 'database is locked':
               raise Exception('database is locked')
           logger.log(__name__, 'No db file found, creating a new database.\n')
           self.create_database()

    def create_database(self):
        self.con = sqlite.connect(DBFILE,isolation_level=None)
        self.con.isolation_level = 'IMMEDIATE'
        self.cur = self.con.cursor()
        sql = open('index/sql.txt','r').read()
        logger.log(__name__,sql)
        self.cur.executescript(sql)
        self.con.commit()
        logger.log(__name__,'Database created.\n')


    def addWord(self,word,posid,autocommit=True):
        self.cur.execute(INS_DICT,(word,posid))
        if autocommit: self.commit()

    def addPos(self,posstr,autocommit=True,needid=True):
        self.cur.execute(INS_POS,(posstr,))
        if autocommit: self.commit()
        if needid:
           return self.cur.execute(SEL_MAX_POS).fetchone()[0]

    def updatePos(self,posid,posstr,autocommit=True):
        self.cur.execute(UPD_POS,(posstr,posid))
        if autocommit: self.commit()

    def getPos(self,posid):
        return self.cur.execute(SEL_POS,(posid,)).fetchone()[1]

    def getDoc(self,docid):
        return self.cur.execute(SEL_DOC,(docid,)).fetchone()[1]

    # select a word with its' posinfo from database
    def getWord(self,word):
        return self.cur.execute(SEL_WORD,(word,)).fetchone()    

    def getPosByWord(self,word):
        result = self.cur.execute(SEL_POS_BY_WORD,(word,)).fetchone()
        if not result:return None
        return result[0]

    def addDoc(self,filename):
        a = self.cur.execute(SEL_DOC_NAME,(filename,)).fetchone() 
        if a != None :
           self.commit()
           print 'Doc Already Added'
           return a[0]
        self.cur.execute(INS_DOC,(filename,))    
        self.commit()
        return self.cur.execute(SEL_MAX_DOCID).fetchone()[0]

    def addSnap(self,text,docid,encoding,dochash):
        self.cur.execute(INS_SNAP,(text,docid,time.time(),encoding,dochash)) 
        self.commit()

    def getPathByDocid(self,docid):
        rs = self.cur.execute(SEL_DOCPATH,(docid,)).fetchone()
        self.commit()
        if not rs:return None
        return rs[0]   

    def getSnap(self,docid):
        snap = self.cur.execute(SEL_SNAP,(docid,)).fetchone()
        if not snap:return None
        return snap

    def getDocCount(self):
        a = self.cur.execute(SEL_DOC_COUNT).fetchone()[0]
        self.commit()
        return a

    def getLastDoc(self):
        last = 'No file has been indexed at this time.'
        try:
            maxdocid = self.cur.execute(SEL_LAST_INDEXD).fetchone()[0]    
            last = self.cur.execute(SEL_DOCPATH,(maxdocid,)).fetchone()[0]  
        except Exception,ext:
            print Exception,ext
            self.commit()
        return last

    def checkHash(self,dochash):
        a = self.cur.execute(SEL_CHK_HASH,(dochash,)).fetchone()
        self.commit()
        if a == None: return False
        return True
    
    def commit(self):
        self.con.commit()
        
    def x__del__(self):
        global pool
        pool.giveback(self)
        
        
class obj_pool:
    import copy
    def __init__(self,inst,count=20):
        self.count = count
        self.conlist = []
        for i in range(count):
            new_inst = inst()
            new_inst.inuse = False
            self.conlist.append(new_inst)
            
    def get(self):
        for k in self.conlist:
            if k.inuse == False:
                k.inuse = True
                return k
    
    def giveback(self,inst):
        for k in self.conlist:
            if k == inst:
                k.inuse = False


def wait_for_lock(block=True):
     info = ''
     while True:
         try:
             d = database()
             return d
         except Exception,inst:
             if info == '':
                        info = 'Database has been locked, some other thread is busy with it...'
                        print info
             if block == False:       
                return None
             time.sleep(.5)
             continue
         
                
#pool = obj_pool(database,20)                

if vglobals.DB_IN_USE == False:
    # first time init some statistics
    db = wait_for_lock()
    doc_count = db.getDocCount()
    last_index = db.getLastDoc()
    vglobals.DB_IN_USE = True
    vglobals.LAST_INDEX = last_index
    vglobals.FILE_INDEXD = doc_count